How to: Create a 3-D reference in a function.
Solution:
Select the desired cell, press F2 to display the function, and change the appropriate reference to a 3-D reference.
NOTE: A 3-D reference can be used to pull together information from multiple, adjacent worksheets into a consolidation sheet. For example, a 3-D reference can be used to sum up the values in the same range in each of the included sheets. The functions that can use 3-D references include: AVERAGE(), COUNT(), COUNTA(), MAX(), MIN(), PRODUCT(), STDEV(), STDEVP(), SUM(), VAR(), VARP().
1) Open the desired workbook.
2) Select the appropriate worksheet.
3) Select the cell containing the function.
4) Press F2 to display the function.
5) Place the insertion point at the reference to be edited.
6) Type the name of the first sheet to be referenced.
7) Type : (a colon).
8) Type the name of the last sheet to be referenced.
9) Type ! (an exclamation mark).
10) Type a specific cell or range reference.
EXAMPLE: To add the values of the cell C4 on sheets 1 through 4 of a workbook, use the SUM() function with a 3-D reference as follows:
=SUM(SHEET1:SHEET4!C4)
11) Press ENTER.
